<?php

/**
 * This file is part of the Pagizer package.
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @copyright   Copyright (c) 2010 Advisa (http://www.advisa.fr)
 * @author      Pagizer Core Team <team@pagizer.org>
 * @package     pagizer
 * @subpackage  website
 */

/**
 * This patch will convert tables into the new format
 */
class core_module_backoffice_0_1_2_Patch001 extends f_package_Patch
{
    private $dbProvider;

    public function patch()
    {
        $this->dbProvider = f_database_Provider::getInstance();

        $this->initialTables();

        $tables = $this->dbProvider->setQuery('SHOW TABLES')->execute()->getQueryResults(PDO::FETCH_COLUMN);

        foreach($tables as $table)
        {
            preg_match('/([m|w])_(.*?)_doc_(.*)/', $table, $matches);

            if(!empty($matches))
            {
                $isI18n = $this->isI18n($matches);
                $isRevision = $this->isRevision($matches);

                $this->alterTable($table, $isI18n, $isRevision);

                //$this->renameTable($table, $matches);
            }            
        }
        
        $this->alterInitialKeys();

        foreach($tables as $table)
        {
            preg_match('/([m|w])_(.*?)_doc_(.*)/', $table, $matches);

            if(!empty($matches))
            {
                $isI18n = $this->isI18n($matches);
                $isRevision = $this->isRevision($matches);

                $this->renameTable($table, $matches);
            }
        }
	
	$tables = $this->dbProvider->setQuery('SHOW TABLES')->execute()->getQueryResults(PDO::FETCH_COLUMN);
	
	foreach($tables as $table)
        {
            preg_match('/([m|w])_(.*?)__(.*)/', $table, $matches);

            if(!empty($matches))
            {
                $isI18n = $this->isI18n($matches);
                $isRevision = $this->isRevision($matches);

                $this->alterTableKeys($table, $isI18n, $isRevision);
            }
        }	
    }

    private function alterInitialKeys()
    {
        $sql = 'ALTER TABLE `f_document` CHANGE `document_id` `document_uid` INT( 11 ) NOT NULL AUTO_INCREMENT';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_acl` ADD FOREIGN KEY ( `document_uid` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_relation` ADD FOREIGN KEY ( `parent_id` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_relation` ADD FOREIGN KEY ( `child_id` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_target` ADD PRIMARY KEY ( `document_uid` ) ;';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_target` ADD FOREIGN KEY ( `document_uid` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_temp` ADD PRIMARY KEY ( `document_uid` ) ';
        $this->dbProvider->executeQuery($sql);
        $sql = 'ALTER TABLE `f_temp` ADD FOREIGN KEY ( `document_uid` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;';
        $this->dbProvider->executeQuery($sql);
    }

    private function initialTables()
    {
        $scripts = array(
        "ALTER TABLE `f_acl` CHANGE `document_id` `document_uid` INT( 11 ) NOT NULL;",
        "ALTER TABLE `f_acl` ADD PRIMARY KEY ( `document_uid` , `user_id` );",
        "ALTER TABLE `f_temp` CHANGE `document_id` `document_uid` INT( 11 ) NOT NULL;",
        "ALTER TABLE `f_relation` DROP FOREIGN KEY `f_relation_ibfk_1` ;",
        "ALTER TABLE `f_relation` DROP FOREIGN KEY `f_relation_ibfk_2` ;",
        "ALTER TABLE `f_target` CHANGE `document_id` `document_uid` INT( 11 ) NOT NULL "
         );

        foreach($scripts as $script)
        {
            $this->dbProvider->executeQuery($script);
        }
    }

    private function isI18n($matches)
    {
        return strpos($matches[3], '_i18n') > 0;
    }

    private function isRevision($matches)
    {
        return strpos($matches[3], '_revisions') > 0;
    }

    private function alterTable($table, $isI18n, $isRevision)
    {
        if($table == 'm_form_doc_field')
        {
            $sql = 'ALTER TABLE `m_form_doc_field` DROP FOREIGN KEY `m_form_doc_field_ibfk_1`  ;';
            $this->dbProvider->executeQuery($sql);
            return false;
        }
        
        $sql = "ALTER TABLE `$table` DROP FOREIGN KEY `".$table."_ibfk_1`  ;";
        $this->dbProvider->executeQuery($sql);
        $sql = "ALTER TABLE `$table` CHANGE `document_id` `document_uid` INT( 11 ) NOT NULL DEFAULT '0'";
        $this->dbProvider->executeQuery($sql);
        $sql = "ALTER TABLE `$table` DROP INDEX `document_id` ";
        $this->dbProvider->executeQuery($sql);

        if(!$isI18n && !$isRevision)
        {
            $sql = "ALTER TABLE `$table` ADD `document_id` INT( 11 ) NOT NULL AUTO_INCREMENT FIRST , ADD KEY (`document_id`), ADD PRIMARY KEY ( `document_uid` );";
            $this->dbProvider->executeQuery($sql);
        }
    }

    private function alterTableKeys($table, $isI18n, $isRevision)
    {
        if($table == 'm_form__field')
        {
            $sql = "ALTER TABLE `m_form__field` ADD FOREIGN KEY ( `recordId` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;";
            $this->dbProvider->executeQuery($sql);
            return false;
        }

        if(!$isI18n && !$isRevision)
        {
            $sql = "ALTER TABLE `$table` ADD FOREIGN KEY ( `document_uid` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;";
            $this->dbProvider->executeQuery($sql);
        }
        else if ($isI18n && !$isRevision)
        {
            $originalTable = str_replace('_i18n', '', $table);
            $sql = "ALTER TABLE `$table` ADD `document_id` INT( 11 ) NOT NULL FIRST ";
            $this->dbProvider->executeQuery($sql);
            $sql = "ALTER TABLE `$table` ADD PRIMARY KEY ( `document_id` , `document_uid` ) ;";
            $this->dbProvider->executeQuery($sql);
            $sql = "ALTER TABLE `$table` ADD FOREIGN KEY ( `document_id` ) REFERENCES `$originalTable`  (`document_id`) ON DELETE CASCADE ;";
            $this->dbProvider->executeQuery($sql);
            $sql = "ALTER TABLE `$table` ADD FOREIGN KEY ( `document_uid` ) REFERENCES `f_document` (`document_uid`) ON DELETE CASCADE ;";
        }
        elseif ($isRevision)
        {
            $sql = "DROP TABLE `$table` ";
            $this->dbProvider->executeQuery($sql);
        }
    }

    private function renameTable($table, $matches)
    {
        $newName = $matches[1].'_'.$matches[2].'__'.$matches[3];
        $renameSql = "RENAME TABLE `$table` TO `$newName` ;";
        $this->dbProvider->executeQuery($renameSql);
    }
}